#encoding=utf-8

from scrapy import log
from  scrapy.spider import BaseSpider
from scrapy.selector import HtmlXPathSelector
from scrapy.contrib.linkextractors.sgml import SgmlLinkExtractor
from scrapy.contrib.spiders import CrawlSpider, Rule
from livescore.items import MatchItem, HighlightItem, SquadItem
from scrapy.http import Request

import MySQLdb
from livescore.db_settings import MySQLSettings
import random
import re
import time
from datetime import datetime, date, timedelta
from time import gmtime, strftime


class  LiveScoreSpider(CrawlSpider):
    name = "soccerway_update"
    allowed_domains =['www.soccerway.com']
    start_urls = ['http://www.soccerway.com/']

    def parse(self, response):

        while (1):
            myTime = datetime.utcnow()  + timedelta(hours=1) 
            myToday = datetime.strftime(myTime, '%Y-%m-%d')
            print myToday
            #time.sleep(60)        

            db = MySQLdb.connect(MySQLSettings.host, MySQLSettings.username, MySQLSettings.password, MySQLSettings.db_name)
            cursor = db.cursor()
            query = """select url, kickOff from Url_Matches use index(idx_dates) where `date` = '""" + str(myToday) + "' order by kickOff;"
            #print query 
            cursor.execute(query)
            db.commit()
            rows = cursor.fetchall()

            wait_list = []
            for row in rows:
                if row[1] == '-838:59:59':
                    yield Request(row[0], dont_filter = True, callback =self.parse_match)
                    continue
                else:
                    wait_list.append(self.time_to_int(str(row[1])))
                    print row[1]

            gmt1_time = datetime.utcnow() + timedelta(hours=1)
            gmt1_time = datetime.strftime(gmt1_time, "%Y-%m-%d %H:%M:%S") 
            gmt1_time = gmt1_time[10:].strip()
            print gmt1_time, '--- gmt1_time'

            gmt1_time = self.time_to_int(gmt1_time)
            print 'now_int:', gmt1_time

            next_int = wait_list[0]
            print 'next_int: ', next_int

            while next_int > gmt1_time + 60:
                print "Sleep time...", next_int - gmt1_time, "seconds!"
                time.sleep(60*10)
                # recall gtm1_time
                gmt1_time = datetime.utcnow() + timedelta(hours=1)
                gmt1_time = datetime.strftime(gmt1_time, "%Y-%m-%d %H:%M:%S") 
                gmt1_time = gmt1_time[10:].strip()
                gmt1_time = self.time_to_int(gmt1_time)

            max_index = len(rows)-1
            end_time = wait_list[max_index]
            print 'end_int:', end_time

            min_index = 0
            active_url = []
            extra_wait = 150*60
            sleep_t = 15
            
            while(gmt1_time <= end_time + extra_wait):
                while min_index <= max_index and gmt1_time + 60 >= wait_list[min_index]:
                    active_url.append(min_index)
                    if sleep_t > 2:
                        sleep_t = sleep_t - 1
                    print min_index, " is actived..."
                    min_index = min_index + 1
                gmt1_time = gmt1_time + sleep_t
                print "Time sleep ...", sleep_t, " seconds"
                time.sleep(sleep_t)
                for i in active_url:
                    if gmt1_time > wait_list[i] + extra_wait:
                        active_url.remove(i)
                        sleep_t = sleep_t + 0.5
                        print i, " is deactived..."
                    else:
                        yield Request(rows[i][0], dont_filter = True, callback =self.parse_match)
                print len(active_url), " url(s) is being actived..."
                random.shuffle(active_url)
                

            # Wait until next day
            while (1):    
                myNextTime = datetime.utcnow()
                myNextDate = datetime.strftime(myNextTime, '%Y-%m-%d')
                if myToday == myNextDate:
                    time.sleep(60*60)
                else:
                    break


    def time_to_int(self, value):
        st = value.split(':')
        tmp = int(st[0])*60*60 + int(st[1])*60 + int(st[2])
        return tmp

    # Parse match
    def parse_match(self, response):
        hxs = HtmlXPathSelector(response)
        
        items = []

        m = MatchItem()
        m['ID_Match'] = ''
        m['home'] = ''
        m['away'] = ''
        m['ID_Home'] = -1
        m['ID_Away'] = -1
        m['stadium'] = ''
        m['gameWeek'] = -1
        m['kickOff'] = '-838:59:59'
        m['league'] = ''
        m['date'] = ''
        m['homeScore'] = -1
        m['awayScore'] = -1
        m['penHomeScore'] = -1
        m['penAwayScore'] = -1
        m['gameMinute'] = 0

        # Fetching ID_Match
        if (re.search(r'\d{6,11}', response.url)):
            ID_Match = re.search(r'\d{6,11}', response.url).group(0)
            m['ID_Match'] = ID_Match
        
        # Fetching teams
        team = hxs.select('/html/body/div[2]/div[2]/div[2]/div/div/div[2]/div/div/div/div/div/h3/a/text()').extract()
        if team:
            m['home'] = team[0].strip()
            m['away'] = team[1].strip()
            url_logo = hxs.select('//div[@class="logo"]/a/img/@src').extract()
            url_logo[0] = url_logo[0].replace('150x150', '30x30')
            url_logo[1] = url_logo[1].replace('150x150', '30x30')
            m['home_logoUrl'] = url_logo[0]
            m['away_logoUrl'] = url_logo[1]
        else:
            pass

        # Fetching score
        score = hxs.select('//h3[@class="thick scoretime "]/text()').extract()
        if score:
            if len(score) > 1:
                score = score[1].strip()
            else:
                score = score[0].strip()
            if score.find("Postponed") < 0:
                hyphen = score.find('-')
                m['homeScore'] = score[:hyphen-1]
                m['awayScore'] = score[hyphen+2:]
        # Fetching league
        league = hxs.select('/html/body/div[2]/div[2]/div[2]/div/div/div[2]/div/div/div/div[2]/div[2]/div/dl/dd/a/text()').extract()
        if not league:
            league = hxs.select('/html/body/div[2]/div[2]/div[2]/div/div/div[2]/div/div/div/div[2]/div[2]/div/dl/dd/a[2]/text()').extract()
        m['league'] = league[0].strip()

        # Fetching date
        date = re.search('\d{4}\/\d{2}\/\d{2}', response.url)
        m['date'] = self.process_date(date.group(0))

        # Is UEFA's league
        if (m['league'].find('UEFA') == 0) or (m['league'].find('Copa') == 0):
            kickOff = hxs.select('/html/body/div[2]/div[2]/div[2]/div/div/div[2]/div/div/div/div[2]/div[2]/div/dl/dd[3]/span/text()').extract()
            if kickOff:
                m['kickOff'] = kickOff[0].strip()
            stadium = hxs.select('/html/body/div[2]/div[2]/div[2]/div/div/div[2]/div/div/div/div[2]/div[2]/div[4]/dl/dd/a/text()').extract()
            if not stadium:
                stadium = hxs.select('/html/body/div[2]/div[2]/div[2]/div/div/div[2]/div/div/div/div[2]/div[2]/div[3]/dl/dd/a/text()').extract()
            if stadium:
                m['stadium'] = stadium[0].strip()
        else: # national leagues
            if league[0].find('FA') == 0:
                kickOff = hxs.select('/html/body/div[2]/div[2]/div[2]/div/div/div[2]/div/div/div/div[2]/div[2]/div/dl/dd[3]/span/text()').extract()
                if kickOff:
                    m['kickOff'] = kickOff[0].strip()
                else:
                    pass
                    #print response.url 
                    #time.sleep(5)
            else:
                gameWeek = hxs.select('/html/body/div[2]/div[2]/div[2]/div/div/div[2]/div/div/div/div[2]/div[2]/div/dl/dd[3]/text()').extract()
                m['gameWeek'] = gameWeek[0]
                
                kickOff = hxs.select('/html/body/div[2]/div[2]/div[2]/div/div/div[2]/div/div/div/div[2]/div[2]/div/dl/dd[4]/span/text()').extract()
                if kickOff:
                    m['kickOff'] = kickOff[0].strip()
                else:
                    pass
                    #print response.url 
                    #time.sleep(5)
            stadium = hxs.select('/html/body/div[2]/div[2]/div[2]/div/div/div[2]/div/div/div/div[2]/div[2]/div[3]/dl/dd/a/text()').extract()
            if not stadium:
                stadium = hxs.select('/html/body/div[2]/div[2]/div[2]/div/div/div[2]/div/div/div/div[2]/div[2]/div[3]/dl/dd[2]/text()').extract()
                if not stadium:
                    stadium = hxs.select('/html/body/div[2]/div[2]/div[2]/div/div/div[2]/div/div/div/div[2]/div[2]/div[2]/dl/dd/a/text()').extract()
            if stadium:
                m['stadium'] = stadium[0].strip()

        full_time = hxs.select('/html/body/div[2]/div[2]/div[2]/div/div/div[2]/div/div/div/div[2]/div[2]/div[2]/dl/dt[2]/text()').extract()
        extra_time = hxs.select('/html/body/div[2]/div[2]/div[2]/div/div/div[2]/div/div/div/div[2]/div[2]/div[2]/dl/dt[3]/text()').extract()
        penalties = hxs.select('/html/body/div[2]/div[2]/div[2]/div/div/div[2]/div/div/div/div[2]/div[2]/div[2]/dl/dt[4]/text()').extract()

        if penalties:
            penScore = hxs.select('/html/body/div[2]/div[2]/div[2]/div/div/div[2]/div/div/div/div[2]/div[2]/div[2]/dl/dd[4]/text()').extract()
            penScore = penScore[0].strip()
            hyphen = penScore.find('-')
            m['penHomeScore'] = penScore[:hyphen-1]
            m['penAwayScore'] = penScore[hyphen+2:]
            
            m['gameMinute'] = -2 
        elif extra_time:
            m['gameMinute'] = -2
        elif full_time:
            m['gameMinute'] = -2
        else:
            game_minute = hxs.select('//span[@class="game-minute"]/text()').extract()
            if game_minute:
                if game_minute[0].find("HT") > -1:
                    m['gameMinute'] = -1
                else:
                    m_time = re.findall(r'\d+', game_minute[0])
                    if len(m_time) == 1:
                        m['gameMinute'] = float(m_time[0])
                    else:
                        m['gameMinute'] = float(m_time[0]) + float(m_time[1])
 
        m['url'] = response.url
        m['crawledDate'] = datetime.now()
        items.append(m)
        
        # Highlight and Squad
        order = 0
        for each in hxs.select('//table[@class="playerstats lineups table"]/tbody/tr'):
            order = order + 1
            
            sq = SquadItem()
            if order != 12 and order != 24: # eleven players of home team
                shift_number = each.select('td[@class="shirtnumber"]/text()').extract()
                player = each.select('td[@class="player large-link"]/a/text()').extract()
                sq['ID_Match'] = ID_Match
                if shift_number:
                    sq['shift_number'] = shift_number[0].strip()
                else:
                    sq['shift_number'] = ''
                sq['player'] = player[0].strip()
                if order < 12:
                    sq['team'] = m['home']
                else:
                    sq['team'] = m['away']

                sq['order'] = order
                items.append(sq)

                png_event = each.select('td[@class="bookings"]/span/img/@src').extract()
                time_event = each.select('td[@class="bookings"]/span/text()').extract()

                if time_event:
                    for j in range(0, len(time_event)): 
                        hh = HighlightItem()
                        png = png_event[j].strip()
                        event = self.processing_png(png_event[j])
                        hh['ID_Match'] = ID_Match
                        hh['player'] = player[0].strip()
                        hh['kind'] = event
                        hh['pngUrl'] = png
                        
                        m_time = re.findall(r'\d+',time_event[j])
                        if len(m_time) == 1:
                            hh['gameMinute'] = float(m_time[0])
                        elif len(m_time) == 2:
                            hh['gameMinute'] = float(m_time[0]) + float(m_time[1])*0.1

                        if order < 12:
                            hh['team'] = m['home']
                        else:
                            hh['team'] = m['away']
                        items.append(hh)
            else:
                coach = each.select('td[@colspan]/a/text()').extract()
                sq['shift_number'] = -1
                sq['player'] = coach[0].strip()
                sq['ID_Match'] = ID_Match
                
                if order == 12:
                    sq['team'] = m['home']
                else:
                    sq['team'] = m['away']
                sq['order'] = order
                items.append(sq)

        for each in hxs.select('//table[@class="playerstats lineups substitutions table"]/tbody/tr'):
            order = order + 1
            sq = SquadItem()
            shift_number = each.select('td[@class="shirtnumber"]/text()').extract()
            player = each.select('.//p[@class="substitute substitute-in"]/a/text()').extract()
            sq['ID_Match'] = ID_Match
            
            if shift_number:
                sq['shift_number'] = shift_number[0].strip()
            else:
                sq['shift_number'] = ''
            sq['player'] = player[0].strip()
            if order == 32:
                sq['team'] = m['home']
            else:
                sq['team'] = m['away']
            sq['order'] = order
            items.append(sq)

            png_event = each.select('td[@class="bookings"]/span/img/@src').extract()
            time_event = each.select('td[@class="bookings"]/span/text()').extract()

            if time_event:
                for j in range(0, len(time_event)): 
                    hh = HighlightItem()
                    event = self.processing_png(png_event[j])
                    png = png_event[j].strip()
                    hh['ID_Match'] = ID_Match
                    hh['player'] = player[0].strip()
                    hh['kind'] = event
                    hh['pngUrl'] = png

                    m_time = re.findall(r'\d+',time_event[j])
                    if len(m_time) == 1:
                        hh['gameMinute'] = float(m_time[0])
                    elif len(m_time) == 2:
                        hh['gameMinute'] = float(m_time[0]) + float(m_time[1])*0.1
                    
                    if order == 32:
                        hh['team'] = m['home']
                    else:
                        hh['team'] = m['away']
                    items.append(hh)

            # substitutes
            sub_png_event = each.select('.//p[@class="substitute substitute-in"]/img/@src').extract()
            player_out = ""
            if sub_png_event:
                sub_event = self.processing_png(sub_png_event[0])
                if sub_png_event == "SI":
                    h = HighlightItem()  
                    player_out =  each.select('.//p[@class="substitute substitute-out"]/a/text()').extract()
                    time_event = each.select('.//p[@class="substitute substitute-out"]/text()').extract()
                    #print time_event[1]
                    #time.sleep(3)
                    h['ID_Match'] = ID_Match
                    h['player'] = player[0].strip()
                    h['sub_player'] = player_out[0].strip()
                    h['kind'] = sub_png_event
                    png = sub_png_event[0].strip()
                    h['pngUrl'] = png
                    m_time = re.findall(r'\d+',time_event[1])
                    if len(m_time) == 1:
                        h['gameMinute'] = float(m_time[0])
                    elif len(m_time) == 2:
                        h['gameMinute'] = float(m_time[0]) + float(m_time[1])*0.1
                   
                    if order == 32:
                        h['team'] = m['home']
                    else:
                        h['team'] = m['away']
                    items.append(h)
        return items


    # process to get date
    def process_date(self, date):
        return datetime.strptime(date.encode('ascii', 'ignore'),"%Y/%m/%d")
    # process to get png_url
    def processing_png(self, href):
        pos = href.find('.png')
        event = href[pos-2:pos].strip('/')
        return event